package com.DeliveryProgram.www.dao;

import com.DeliveryProgram.www.po.*;
import com.DeliveryProgram.www.util.JDBCutil;
import com.DeliveryProgram.www.util.Scanners;
import com.DeliveryProgram.www.util.Screens;
import com.mysql.cj.jdbc.JdbcConnection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**负责从数据库中获取与商家有关信息的类*/
public class BusLoad {

   private static Connection conn=null;
    private static Statement stmt=null;
    private static ResultSet rs=null;

    /**初始化该商户信息*/
    public static void Read(Business business){

        try {
            conn= JDBCutil.getConnection();
            String sql="SELECT * FROM business where name='"+business.getName()+"'";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            rs.next();
            business.setBid(rs.getInt("bid"));
            business.setShopname(rs.getString("shopname"));
            business.setIndroduce(rs.getNString("introduce"));

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }


    }

    /**对初次登录的商户设定商户基本信息录入数据库中*/
    public static boolean Create(Business business){
        /**获取店名和店介绍*/
        System.out.println("请输入店名（不多于10字）");
        business.setShopname(Scanners.getScanner());
        System.out.println("请输入店铺简介（不多于30字）");
        business.setIndroduce(Scanners.getScanner());
        try {
            conn=JDBCutil.getConnection();
            String sql="UPDATE business SET shopname='"+business.getShopname()
                    +"',introduce='"+business.getIndroduce()
                    +"' WHERE NAME='"+business.getName()+"'";
            stmt=conn.createStatement();
            stmt.executeUpdate(sql);
            Screens.clear();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(stmt,conn);
        }


        System.out.println("店名重复或字符过多，请重新输入");
        return false;
    }

    /**从数据库中获取菜单数据*/
    public static void loadMenu(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT * FROM menu where business="+bu.getBid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                /**将菜品的数据存入菜单集合*/
                bu.setMenu(new Menu(rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("introduce"),
                        rs.getInt("price")));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }

    }

    /**从数据库中获取折扣数据*/
    public static void loadCoupons(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT * FROM coupons where business ="+bu.getBid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()) {
                /**不读取发完的折扣*/
                if (rs.getInt("mount")!=0) {
                    bu.setCoupons(new Coupons(rs.getInt("id"), rs.getInt("mount"), rs.getDouble("discount")));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
         JDBCutil.close(rs,stmt,conn);
        }
    }

    /**从数据库中获取订单历史*/
    public static void loadOrder(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT orders.`id`," +
                    "orders.`date`," +
                    "orders.`isComplete`," +
                    "orders.`money`," +
                    "user.`name`," +
                    "address.`ad`," +
                    "address.`ph`," +
                    "address.`id` " +
                    "FROM " +
                    "orders,address,USER " +
                    "WHERE " +
                    "orders.`address`=address.`id` " +
                    "AND orders.`user`=user.`uid` " +
                    "and orders.`business`="+bu.getBid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);

            while(rs.next()){
            bu.setOrders(new Order(
                    rs.getInt(1),
                    rs.getInt(3),
                    new Address(rs.getInt(8), rs.getNString(6),rs.getNString(7),rs.getNString(5) )
                    ,rs.getDate(2),
                    rs.getInt(4)));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }

    }

    /**每个订单从数据库中读取菜单*/
    public static List<Menu> loadOrder(int id) {
        List<Menu> mune=new ArrayList<Menu>();
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT *FROM odmu where orde="+id;
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                mune.add(new Menu(rs.getInt("id"),rs.getNString("menu"), rs.getInt("mount") ));
            }
            return mune;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }


        return null;
    }

    /**获取商家已经活动的收益*/
    public static void loadPrice(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT SUM(money) FROM orders WHERE business="+bu.getBid()+" AND iscomplete=3";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            if(rs.next()){
                bu.setEarn(rs.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**获取商家的评论*/
    public static void loadBuComment(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT comment.`id`,comment.`comment`,comment.`date` " +
                    "FROM COMMENT,businesscomment " +
                    "WHERE businesscomment.`business`="+bu.getBid()+" AND businesscomment.`id`=comment.`id`";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                bu.setComments(new Comment(rs.getInt(1),bu.getShopname(),bu.getShopname(),rs.getNString(2),rs.getDate(3)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**获取用户对商家的评论*/
    public static void loadUsComment(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT comment.`id`,comment.`comment`,comment.`date`,user.`name` FROM USER,COMMENT,usercomment WHERE user.`uid`=usercomment.`id` AND usercomment.`id`=comment.`id` AND usercomment.`business`="+bu.getBid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                bu.setComments(new Comment(rs.getInt(1),rs.getNString(4),bu.getName(),rs.getNString(2),rs.getDate(3)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**获取创建新评论*/
    public static void getNewComment(String comment,Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT id,date FROM COMMENT WHERE COMMENT='"+comment+"'";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            rs.next();
            bu.setComments(new Comment(rs.getInt(1),bu.getShopname(),bu.getShopname(),comment,rs.getDate(2)));

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCutil.close(rs,stmt,conn);
        }

    }

    /**获取新菜单的Id*/
    public static void getNewId(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT id FROM menu WHERE business="+bu.getBid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            rs.next();
            bu.getMenu().get(bu.getMenu().size()-1).setId(rs.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**获取新折扣id*/
    public static void getNewCouponsId(Business bu){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT id FROM coupons WHERE business="+bu.getBid();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            rs.next();
            bu.getCoupons().get(bu.getCoupons().size()-1).setId(rs.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

    /**在顾客系统中获取所有商户基本信息*/
    public static void getAllBusiness(List<Business> list){
        try {
            conn=JDBCutil.getConnection();
            String sql="SELECT* FROM business";
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                list.add(new Business(rs.getInt(1),rs.getString(3),rs.getNString(4)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCutil.close(rs,stmt,conn);
        }
    }

}
